﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace WIC_Data_Manager
{
    class SQLDataBase
    {
        private string ServerIP;
        private string ServerPort;
        private string DataBaseName;
        private string UserName;
        private string Password;
        private string TableName;

        private MySqlConnection connection;

        public SQLDataBase(string ip, string databasename, string username, string password)
        {

            ServerIP = ip;
            ServerPort = "3306";
            DataBaseName = databasename;
            UserName = username;
            Password = password;

        }

        public void SetPort(string port)
        {
            ServerPort = port;
        }

        public void SetDataBaseName(string databasename)
        {
            DataBaseName = databasename;
        }

        public void Connect()
        {
            try
            {
                string MyConString = "SERVER=" + ServerIP + ";" +
                    "PORT=" + ServerPort + ";" +
                    "DATABASE=" + DataBaseName + ";" +
                    "UID=" + UserName + ";" +
                    "PASSWORD=" + Password + ";";
                connection = new MySqlConnection(MyConString);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Error. This incident will be reported.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public string SimpleQuery(string query)
        {
            string result = "";

            try
            {
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;
                command.CommandText = query;
                connection.Open();
                Reader = command.ExecuteReader();

                while (Reader.Read())
                {
                    string thisrow = "";
                    for (int i = 0; i < Reader.FieldCount; i++)
                        thisrow += Reader.GetValue(i).ToString() + " ";
                    result += thisrow;
                }

                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Error. This incident will be reported.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            result = result.TrimEnd(' ');
            return result;
        }

        public string SelectFromQuery(string select, string from)
        {
            string result = "";

            try
            {
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;
                command.CommandText = "SELECT " + select + " FROM " + from;
                connection.Open();
                Reader = command.ExecuteReader();

                while (Reader.Read())
                {
                    string thisrow = "";
                    for (int i = 0; i < Reader.FieldCount; i++)
                        thisrow += Reader.GetValue(i).ToString() + " ";
                    result += thisrow;
                }

                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Error. This incident will be reported.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }


            result = result.TrimEnd(' ');
            return result;
        }

        public string SelectFromWhereQuery(string select, string from, string where)
        {
            string result = "";

            try
            {
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;
                command.CommandText = "SELECT " + select + " FROM " + from + " WHERE " + where;
                connection.Open();
                Reader = command.ExecuteReader();

                while (Reader.Read())
                {
                    string thisrow = "";
                    for (int i = 0; i < Reader.FieldCount; i++)
                        thisrow += Reader.GetValue(i).ToString() + " ";
                    result += thisrow;
                }

                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Error. This incident will be reported.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            result = result.TrimEnd(' ');
            return result;
        }

        public List<string> LargeQuery(string query)
        {
            List<string> result = new List<string>();

            try
            {
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;
                command.CommandText = query;
                connection.Open();
                Reader = command.ExecuteReader();

                while (Reader.Read())
                {
                    string thisrow = "";
                    for (int i = 0; i < Reader.FieldCount; i++)
                        thisrow += Reader.GetValue(i).ToString() + " ";
                    result.Add(thisrow);
                }

                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Error. This incident will be reported.", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return result;

        }

    }
}
